package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sg.hms.Dao.XianshiEmployeesDao;
import com.sg.hms.Model.Employee;
import com.sg.hms.Util.JDBCUtil;

public class XianshiEmployeesDaoImpl implements XianshiEmployeesDao{

	@Override
	public List<Employee> xianshiEmployees(int start, int pageSize) {
		// TODO Auto-generated method stub
		List<Employee> list = new ArrayList<Employee>();
		StringBuffer sql = new StringBuffer("select elId,elName,dName,salary,state from employee,department where employee.dId=department.dId order by elId limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Employee empl1 = new Employee();//实例化一个员工
				empl1.setElId(rs.getInt("elId"));//获取员工编号
				empl1.setElName(rs.getString("elName"));//获取员工姓名
				empl1.setdName(rs.getString("dName"));//获取员工部门名字
				empl1.setSalary(rs.getDouble("salary"));//获取员工工资
				empl1.setState(rs.getString("state"));//员工是否离职
				list.add(empl1);
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int employeesCount(int elId) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(elId) scount from employee");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int employeesCount(int elId, String searchName) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(elId) scount from employee where elName like ?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				count = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<Employee> xianshiEmployees(int start, int pageSize, String searchName) {
		// TODO Auto-generated method stub
		List<Employee> list = new ArrayList<Employee>();
		StringBuffer sql = new StringBuffer("select elId,elName,dName,salary,state from employee,department where employee.dId=department.dId and elName like ? order by elId limit ?,?");
		Connection con = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchName+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Employee empl1 = new Employee();//实例化一个员工
				empl1.setElId(rs.getInt("elId"));//获取员工编号
				empl1.setElName(rs.getString("elName"));//获取员工姓名
				empl1.setdName(rs.getString("dName"));//获取员工部门名字
				empl1.setSalary(rs.getDouble("salary"));//获取员工工资
				empl1.setState(rs.getString("state"));//员工是否离职
				list.add(empl1);
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
 
}
